from util.common import *
from db.db_util import dataframe_from_db

if __name__ == '__main__':

    df = dataframe_from_db("""
        SELECT
            vm.UITRA_VIRES_STATE,vm.ACOUNT,vm.NET_ID,ce.end_time, ce.task_bussids,ce.task_name,vm.MENU_URL
        FROM
            TF_SA_UITRA_VIRES_MENU vm
            LEFT JOIN TF_SA_COMPLIANCE_TASK ce ON ce.task_id = vm.UITRA_VIRES_TEST_ID 
        WHERE
            UITRA_VIRES_STATE = '2';
    """)

    df1 = read_excel("source/tf_smp_web_asset2.xlsx")
    records = df1.to_dict(orient='records')
    keys = [item['ID'] for item in records]
    my_dict = dict(zip(keys, records))

    w_sql_list = []
    for row in df.itertuples():
        asset = my_dict[row.NET_ID]
        seep = {
            "id": "COREZX" + random_string(14),
            "url": row.MENU_URL,
            "acount": row.ACOUNT,
            "assetId": row.NET_ID,
            "startTime": row.end_time,
            "name": row.task_name,
            "assetIP": asset['INTRANET_IP'],
            "assetName": asset['ASSET_NAME'],
        }
        message1 = render_template("""
                INSERT INTO TF_SA_CONTENT_RESULT (ID, CONTENT_URL, CONTENT_NAME, RESPONSE_CODE, ACOUNT, EXECUTE_TIME, UPDATE_TIME,
                                  PAY_LOAD, DEPTH, ASSET_ID, SOURCE, BUSINESS_ID, TIPS, ASSET_IP, ASSET_TYPE, PARTNER,
                                  CONTENT_DEPT, APPMAN_DEPT, APPMAN_BOSSRES, APPDEV_FACRES, APPDEV_BOSSRES, APPMAN_FAC,
                                  APPMAN_FACRES, SYS_FACRES, SYS_BOSSRES, IS_ONLINEPJ, ONLINE_ID, ONLINE_CYCLE,
                                  TESTREPORT_ID, MENU_ID, MENU_PATH, IS_ON_NET, FIND_DATE, FIND_TYPE, FINDER,
                                  IS_TESTLOGIN, SENDER, IS_SENDED, SEND_DATE, CHANGE_ID, CLOSE_DATE, CHANGEBUSS_REQID,
                                  CHANGESOFT_REQID, DEVOLEP_DESC, IS_WRONG, REINFORCE_PLANDATE, REINFORCER,
                                  REINFORCE_DATE,
                                  REINFORCE_STATUS, REINFORCE_DESC, REINFORCE_YX, APPBOSS_OPIN, SYSBOSS_OPIN, FAC_OPIN,
                                  REVIEW_RESULT, REVIEW_DATE, REVIEWER, REPORT_STATUS, REPORT_DATE, REBUID_ADVICE,
                                  FACRES,
                                  BOSSRES, OPT_ID, TIMES, REPORT_NUM, TEST_REPORTNUM, ASSET_STATUS, BEAR_BUSS,
                                  HOLE_ENCODE, REVIEW_ATTACH, REVIEW_PROPOSAL, REMARK, RETREAT_ADVICE, FACDEAL,
                                  ASSET_NAME,
                                  RESPONSE_TEXT, IP_VAL)
                VALUES ($id, $url, $name, 200, $acount, 1,
                        NULL, NULL, NULL, $assetId, '内容 合规引擎', NULL, NULL, $assetIP, '1',
                        NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                        $startTime, NULL, '0', NULL, NULL, '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                        '-1', NULL, NULL, NULL, NULL, NULL, '-1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1,
                        NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, $assetName, NULL,
                        NULL);

                      """, seep)
        w_sql_list.append(message1)

    write_file('target/SATP_内容合规中心.sql', w_sql_list)
